package dao;

import bean.Express;
import util.DriverManegerUitl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

public class ExpressDao {
    private Connection conn;
    private PreparedStatement pre;
    private ResultSet resultSet;

    /**
     * 学生端
     *
     * @param studentname
     * @return
     */
    //根据姓名查询快件信息
    public ArrayList<Express> getExpressInfoByStudentname(String studentname) {
        conn = DriverManegerUitl.getConnection();
        ArrayList<Express> list = new ArrayList<>();
        try {

            pre = conn.prepareStatement("select * from express where studentname=?");
            pre.setString(1, studentname);
            resultSet = pre.executeQuery();
            while (resultSet.next()) {
                Express express = new Express();
                express.setStudentname(resultSet.getString("studentname"));
                express.setDormitoryid(resultSet.getString("dormitoryid"));
                express.setArrivetime(resultSet.getDate("arrivetime"));
                express.setCollecttime(resultSet.getDate("collecttime"));
                express.setCollectnum(resultSet.getInt("collectnum"));
                express.setCollectname(resultSet.getString("collectname"));
                express.setCollectphoneid(resultSet.getString("collectphoneid"));
                list.add(express);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DriverManegerUitl.closed(conn, pre, resultSet);
        }
        return null;
    }

    /**
     * 管理员端
     *
     * @return
     */
    //查询快件所有信息
    public ArrayList<Express> getAllExpress() {
        conn = DriverManegerUitl.getConnection();
        ArrayList<Express> list = new ArrayList<>();
        try {
            pre = conn.prepareStatement("select * from express");
            resultSet = pre.executeQuery();
            while (resultSet.next()) {
                Express express = new Express();
                express.setStudentname(resultSet.getString("studentname"));
                express.setDormitoryid(resultSet.getString("dormitoryid"));
                express.setArrivetime(resultSet.getDate("arrivetime"));
                express.setCollecttime(resultSet.getDate("collecttime"));
                express.setCollectnum(resultSet.getInt("collectnum"));
                express.setCollectname(resultSet.getString("collectname"));
                express.setCollectphoneid(resultSet.getString("collectphoneid"));
                list.add(express);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DriverManegerUitl.closed(conn, pre, resultSet);
        }
        return null;
    }

    /**
     * 提交姓名
     * 宿舍号
     * 快递到达时间
     * 收件数量
     *
     * @param express
     */
    //管理员提交快递信息
    public void submit(Express express) {
        conn = DriverManegerUitl.getConnection();
        try {
            pre = conn.prepareStatement("insert into express(studentname,dormitoryid,arrivetime,collectnum) values(?,?,?,?)");
            pre.setString(1, express.getStudentname());
            pre.setString(2, express.getDormitoryid());
            pre.setDate(3, new java.sql.Date(express.getArrivetime().getTime()));
//            pre.setDate(4,  new java.sql.Date(express.getCollecttime().getTime()));
            pre.setInt(4, express.getCollectnum());
//            pre.setString(6,express.getCollectname());
//            pre.setString(7,express.getCollectphoneid());
            pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DriverManegerUitl.closed(conn, pre, resultSet);
        }
    }

    //快递员修改快递信息
//    根据用户名宿舍号
    public void modify(Express express) {
        conn = DriverManegerUitl.getConnection();
        try {
            pre=conn.prepareStatement("update express set  collecttime=?,collectname=?,collectphoneid=? where studentname=? and dormitoryid=?");
            pre.setDate(1,new java.sql.Date(express.getCollecttime().getTime()));
            pre.setString(2,express.getCollectname());
            pre.setString(3,express.getCollectphoneid());
            pre.setString(4,express.getStudentname());
            pre.setString(5,express.getDormitoryid());
            pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,pre,resultSet);
        }

    }
//    删除信息
    //根据姓名和宿舍号
    public void delInfo(String studentname,String dormitoryid){
        conn = DriverManegerUitl.getConnection();
        try {
            pre=conn.prepareStatement("delete from express where studentname=? and dormitoryid=?");
            pre.setString(1,studentname);
            pre.setString(2,dormitoryid);
            pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}